﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;


namespace websitev2.Core
{
    public class UserCore
    {

        private static TimeSpan checktimespan(string inboundSQL)
        {
            string result = Core.SqlAccessCore.ExecuteScalar(inboundSQL);
            if (result == "") { result = "0"; }

            return new TimeSpan(0, 0, Int32.Parse(result));
        }


        public static Models.User.LoggedIn LoadLoggedInUserDetails(Guid UserGUID)
        {

            Models.User.LoggedIn _return = new Models.User.LoggedIn();
            Models.StartStopEntities db = new Models.StartStopEntities();

            var user = db.ssUsers.FirstOrDefault(u => u.UserGUID == UserGUID);
            _return.EmailAddress = user.EmailAddress;
            _return.UserGUID = UserGUID;
            _return.UserID = user.UserID;
            _return.UserName = user.UserName; 
            

            return _return; 

        }

        public static Models.UserGeneralInformationModel LoadUserGen(Guid UserGUID)
        {
            Models.UserGeneralInformationModel returnUserGen = new Models.UserGeneralInformationModel();
            Models.StartStopEntities db = new Models.StartStopEntities();
            var user = db.ssUsers.FirstOrDefault(u => u.UserGUID == UserGUID);

            #region high level stats
            returnUserGen.NumberOfFriends = Int32.Parse(Core.SqlAccessCore.ExecuteScalar("select count (*) from [FriendLink] where UserID1 = " + user.UserID));
            returnUserGen.TrophyCount = Core.SqlAccessCore.ExecuteScalar("select count (*) from [TrophiesUserLink] where UserID = " + user.UserID + " and AwardedYet = 1"); 

            // Set this to be an abitrary number for now. 
            returnUserGen.CurrentLevel = 1;
            returnUserGen.Username = user.UserName;
            returnUserGen.Joined = user.CreatedOn.GetValueOrDefault(); 
            
            #endregion


            #region time spans
            // Time spent browsing
            string _sqlCommand = @"
                select sum(ElapsedTimeSeconds) as ElapsedSeconds 
                from USersAppLog where AppiD  in (select Appid from WindowAppList where typeID=1) and USerID=" + user.UserID + "  and loggeddate ='" + DateTime.Now.ToString("dd MMM yy") + "'";
            returnUserGen.TimeSpentBrowsing = checktimespan(_sqlCommand);

            // Working
            _sqlCommand = @"
                select sum(ElapsedTimeSeconds) as ElapsedSeconds 
 from USersAppLog where AppiD  in (select Appid from WindowAppList where typeID=2) and USerID=" + user.UserID + "  and loggeddate ='" + DateTime.Now.ToString("dd MMM yy") + "'";
            returnUserGen.TimeSpentWorking = checktimespan(_sqlCommand);

            // Playing
            _sqlCommand = @"
                select sum(ElapsedTimeSeconds) as ElapsedSeconds 
 from USersAppLog where AppiD  in (select Appid from WindowAppList where typeID=3) and USerID=" + user.UserID + "  and loggeddate ='" + DateTime.Now.ToString("dd MMM yy") + "'";
            returnUserGen.TimeSpentPlaying = checktimespan(_sqlCommand);

            // Idle
            _sqlCommand = @"
                select sum(ElapsedTimeSeconds) as ElapsedSeconds 
 from USersAppLog where AppiD  =7  and USerID=" + user.UserID + "  and loggeddate ='" + DateTime.Now.ToString("dd MMM yy") + "'";
            returnUserGen.IdleTime = checktimespan(_sqlCommand);

            // Instant Messaging
            _sqlCommand = @"
                 select sum(ElapsedTimeSeconds) as ElapsedSeconds 
 from USersAppLog where AppiD  in (select Appid from WindowAppList where typeID=4) and USerID=" + user.UserID + "  and loggeddate  ='" + DateTime.Now.ToString("dd MMM yy") + "'";
            returnUserGen.TimeSpentMessaging = checktimespan(_sqlCommand);


            // Total Time

            _sqlCommand = @"select  sum(ElapsedTimeSeconds) from UsersAppLog where userid = " + user.UserID + " and appid <> 7  and loggeddate  ='" + DateTime.Now.ToString("dd MMM yy") + "'";
            returnUserGen.ComputerTime = checktimespan(_sqlCommand);

            #endregion

            return returnUserGen;
        }

        public static string ReturnAvatarURL(Guid UserGuid, int AvatarType)
        {
            string _returnURL = "";
            

            if (AvatarType == 1)
            {
                string _fileName = Image.CheckImageExists(UserGuid, 1); 
                if (_fileName!="")
                {
                    _returnURL = "/uploads/images/"+ _fileName; 
                }
                else
                {
                    _returnURL = "/images/icon_pr.png"; 
                }
            }

            if (AvatarType == 2)
            {
                string _fileName = Image.CheckImageExists(UserGuid, 2);
                if (_fileName != "")
                {
                    _returnURL = "/uploads/images/" + _fileName;
                }
                else
                {
                    // We should remove this until Thanos provides us with a better option perhaps. 
                    _returnURL = "/images/icon_pr_small.png";
                }
            }

            return _returnURL; 
        }

        public static  websitev2.Models.AppUsage[] ReturnAppUsage (long UserID, int NumberOfDays, int NumberOfRows)
        {

            DateTime oDate = DateTime.Now.Subtract(new TimeSpan(NumberOfDays,0,0,0,0)); 

           // YYYYmmdd to work out the dates
            string _date = oDate.ToString("dd MMM yy"); 

            string _sqlCommand =@" 
SELECT TOP "+ NumberOfRows.ToString() +  @"  
    SUM(u.elapsedTimeSeconds) AS et, u.AppID, wa.Description
FROM dbo.UsersAppLog u
INNER JOIN WindowAppList wa ON u.AppID = wa.AppID
WHERE
    u.userid = "+ UserID+ @"  
    AND u.appid <> 7 
    AND u.CreatedOn > '"+_date + @"'     -- use ISO-8601 format for extra safety!
GROUP BY 
    u.AppID, wa.Description
ORDER BY
    et DESC

                ";

            // Do the thing

          DataTable oTable =   websitev2.Core.SqlAccessCore.ReturnDataTable(_sqlCommand);

          int _rowsToReturn = NumberOfRows;
            // Just incase not enough rows are returned! Like a user with no data huh! 
          if (oTable.Rows.Count < _rowsToReturn) { _rowsToReturn = oTable.Rows.Count; } 
          websitev2.Models.AppUsage[] oApps = new Models.AppUsage[_rowsToReturn];
          int _currentCount = 0; 

            // Surely there must be an easier way of mapping the data! 
          foreach (DataRow oRow in oTable.Rows)
          {
              websitev2.Models.AppUsage App = new Models.AppUsage();

              App.AppID = (long)oRow["AppID"];
              if (oRow["Description"].ToString() != "")
              {
                  App.ApplicationName = oRow["Description"].ToString();
              }
              else
              {
                  App.ApplicationName = "unclassified"; 
              }
              App.ElapsedTime = new TimeSpan(0, 0, (int)oRow["et"]);
              oApps[_currentCount] = App; 
              _currentCount++; 
          }

          return oApps; 
        }

        public static websitev2.Models.UserTrophy[] ReturnUserTrophy(long UserID, int MaxReturn, bool Awarded)
        {
            int _awarded = 0;
            if (Awarded) { _awarded = 1; } 

            string _sqlCommand = @" 
SELECT  TOP " + MaxReturn.ToString()+@" 
      t.[TrophyID]
      ,t.[AwardedOn]
      ,t.[AwardedYet]
      ,t.[PercentageComplete]
      ,t.[CreatedOn]
      ,t.[ModifiedOn]
      ,t.[FBUpdated], td.tDescription, td.TName, td.tCondition
  FROM [TrophiesUserLink] t 
  inner join Trophies td on t.TrophyID = td.TrophyID
  where t.UserID = " + UserID.ToString() + @" and AwardedYet = " + _awarded;

            DataTable oTable = websitev2.Core.SqlAccessCore.ReturnDataTable(_sqlCommand);
            Models.UserTrophy[] oTophies = new Models.UserTrophy[oTable.Rows.Count];
            int tCount = 0; 
            foreach (DataRow oRow in oTable.Rows)
            {
                websitev2.Models.UserTrophy oTrophy = new Models.UserTrophy();
                if (oRow["AwardedOn"].ToString() != "")
                {
                    oTrophy.AwardedOn = (DateTime)oRow["AwardedOn"];
                }
                oTrophy.AwardedYet = (bool)oRow["AwardedYet"];
                oTrophy.Name = (string)oRow["TName"];
                oTrophy.PercentageComplete = (int)oRow["PercentageComplete"];
                oTrophy.Condition = (string)oRow["tCondition"];
                oTrophy.TrophyID = (int)oRow["TrophyID"];
                
                // Add to the main trophy model
                oTophies[tCount] = oTrophy;
                tCount++; 

            }


            return oTophies; 
            
        }

        /// <summary>
        /// Returns the trophy records which are associated with an application.
        /// </summary>
        /// <param name="AppID"></param>
        /// <returns></returns>
        public static Models.AppTrophy[] ReturnAppTrophy(Int64 AppID)
        {
            string _sqlCommand = @" 
select t.TrophyID, t.AppID, td.TName, td.tDescription, td.tCondition 
from [WindowAppTrophyLink] t 
inner join Trophies td on t.TrophyID = td.TrophyID
where t.AppID= " + AppID.ToString();
            DataTable oTable = websitev2.Core.SqlAccessCore.ReturnDataTable(_sqlCommand);
            Models.AppTrophy[] oTrophies = new Models.AppTrophy[oTable.Rows.Count];
            int tCount = 0; 
            foreach (DataRow oRow in oTable.Rows)
            {
                Models.AppTrophy oApp = new Models.AppTrophy();
                oApp.Condition = oRow["tCondition"].ToString();
                oApp.Description = oRow["tDescription"].ToString();
                oApp.Name = oRow["TName"].ToString();
                oApp.TrophyID = int.Parse(oRow["TrophyID"].ToString()); 
                oTrophies[tCount] = oApp;
                tCount++; 
            }
            return oTrophies; 
        }




    }
}